<?php
  $dbHost = 'localhost';
  $dbSchema = 'macdadi';
  $dbUser = 'macdadi-read';
  $dbPwd = '';

  // SELECT all rows from goals table
  selectTable('goals', $dbHost, $dbSchema, $dbUser, $dbPwd);

  function selectTable($tableName, $host, $schema, $user, $pwd)
  {
    $link = mysql_connect($host, $user, $pwd) 
    or die('Could not connect: ' . mysql_error());

    mysql_select_db($schema) 
    or die('Could not select database');

    $tableName = mysql_real_escape_string($tableName, $link);

    // TODO: escape SQL, avoid injection attacks
    $query = 'SELECT * FROM ' . $tableName;
    $result = mysql_query($query) 
    or die('Query failed: ' . mysql_error());

    printHTML($result);

    // rewind so that can re-use the result-set
    mysql_data_seek($result, 0);
    printJSON($result);
    
    // Free resultset
    mysql_free_result($result);
    
    // Closing connection
    mysql_close($link);
  }

  function printJSON($result)
  {
    $arr = array();
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
      $arr[] = $row;
    }
    print json_encode($arr);
  }

  function printHTML($result)
  {
?>
<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=iso-8559-1">
<title>MACDADI Data</title>
</head>
<body>
<table>
<?php
    // Print the column names as header, from first row
    $line = mysql_fetch_array($result, MYSQL_ASSOC);
    print "\t<tr>\n";
    while ( list( $col_name ) = each( $line ) )
      print "\t\t<th>$col_name</th>\n";
    print "\t</tr>\n";
    mysql_data_seek($result, 0);

    // After resetting result set, print each row's values
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
      print "\t<tr>\n";
      foreach ($line as $col_value) {
        print "\t\t<td>$col_value</td>\n";
      }
      print "\t</tr>\n";
    }
?>
</table>

<p>Script and page created by Ben Suter, copyright 2008 Ben Suter - <a href="http://www.fuzzysymmetry.com">fuzzysymmetry</a></p>

</body>
</html>

<?php
  }
?>    

